using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace Elte.SqlSpIdx
{
    public static class ClusterSearch
    {

        public static void ClusterSearchFillRow(object row, out double ra, out double dec, out double cd, out double count, out double xd)
        {
            double[] rowd = (double[])row;
            ra = rowd[0];
            dec = rowd[1];
            cd = rowd[2];
            count = rowd[3];
            xd = rowd[4];
        }

        public static void ClusterSearchFillRow2(object row, out double r, out double g)
        {
            double[] rowd = (double[])row;
            r = rowd[0];
            g = rowd[1];

        }





        ////Cover index created for this!
        //[Microsoft.SqlServer.Server.SqlProcedure]
        //public static void ClusterSearch2(SqlDouble ra, SqlDouble dec, SqlDouble R)
        //{
        //    double nr = 2 * Math.Sin((((double)(R / 120)) / 180.0 * Math.PI));


        //    double nx, ny, nz, decr, rar;
        //    decr = Math.PI / 180 * (double)dec;
        //    rar = Math.PI / 180 * (double)ra;
        //    nx = Math.Cos(decr) * Math.Cos(rar);
        //    ny = Math.Cos(decr) * Math.Sin(rar);
        //    nz = Math.Sin(decr);
        //    double x, y, z;
        //    //results
        //    double avgra = 0;
        //    double avgdec = 0;
        //    double cd = 0;
        //    double xd = 0;
        //    int count = 0;


        //    //to change, works, speedy, but ugly
        //    double[] r = new double[10000];
        //    double[] gr = new double[10000];
        //    double tempr;
        //    double tempgr;
        //    double tempcd;
        //    string cstring;
        //    //if (SqlContext.IsAvailable)
        //    //{
        //    //SqlContext.Pipe.Send(nx.ToString());
        //    //SqlContext.Pipe.Send(ny.ToString());
        //    //SqlContext.Pipe.Send(nz.ToString());
        //    //SqlContext.Pipe.Send(nr.ToString());
        //    //    cstring = "Context connection=yes";
        //    //    System.Data.SqlClient.SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
        //    //    cs.InitialCatalog = "hgeza";
        //    //    cs.IntegratedSecurity = false;
        //    //    cs.UserID = "hgeza_clr";
        //    //    cs.Password = "Almafa7";
        //    //    cs.DataSource = "newton";
        //    //    cstring = cs.ConnectionString;
        //    //}
        //    //else
        //    //{
        //    //System.Data.SqlClient.SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
        //    //cs.InitialCatalog = "hgeza";
        //    //cs.IntegratedSecurity = false;
        //    //cs.UserID = "hgeza_clr";
        //    //cs.Password = "Almafa7";
        //    //cs.DataSource = "newton";
        //    //cstring = cs.ConnectionString;
        //    //}
        //    using (SqlConnection cn = new SqlConnection("Context connection=yes"))
        //    {

        //        cn.Open();


        //        //SP
        //        SqlCommand ClusterSearchTsql2 = new SqlCommand("ClusterSearchTsql2");
        //        ClusterSearchTsql2.CommandType = CommandType.StoredProcedure;
        //        ClusterSearchTsql2.Parameters.AddWithValue("@x", nx);
        //        ClusterSearchTsql2.Parameters.AddWithValue("@y", ny);
        //        ClusterSearchTsql2.Parameters.AddWithValue("@z", nz);
        //        ClusterSearchTsql2.Parameters.AddWithValue("@r", nr);
        //        ClusterSearchTsql2.Connection = cn;

        //        using (SqlDataReader rs = ClusterSearchTsql2.ExecuteReader())
        //        {
        //            while (rs.Read())
        //            {

        //                //x = rs.GetDouble(0);
        //                //y = rs.GetDouble(0);
        //                //z = rs.GetDouble(0);

        //                //decr = Math.PI / 180 * (double)dec;
        //                //rar = Math.PI / 180 * (double)ra;
        //                //nx = Math.Cos(decr) * Math.Cos(rar);
        //                //ny = Math.Cos(decr) * Math.Sin(rar);
        //                //nz = Math.Sin(decr);
        //                //double x, y, z;

        //                ////avg of ra
        //                //avgra += rs.GetDouble(0);

        //                ////avg of dec
        //                //avgdec += rs.GetDouble(1);

        //                //r values
        //                tempr = rs.GetDouble(3);
        //                r[count] = tempr;

        //                //gr values
        //                tempgr = rs.GetDouble(4);
        //                gr[count] = tempgr;




        //                for (int i = 0; i < count; i++)
        //                {

        //                    tempcd = (r[i] - tempr) * (r[i] - tempr) + (gr[i] - tempgr) * (gr[i] - tempgr);

        //                    //instead of 'avg((s1.r-s2.r)*(s1.r-s2.r)+(s1.gr-s2.gr)*(s1.gr-s2.gr)) as cd'
        //                    cd += tempcd;

        //                    //instead of 'avg(exp(-((s1.r-s2.r)*(s1.r-s2.r)+(s1.gr-s2.gr)*(s1.gr-s2.gr)))) as xd'
        //                    xd += Math.Exp(-tempcd); ;
        //                }
        //                count++;
        //            }
        //        }

        //    }

        //    //Return structure
        //    SqlDataRecord s = new SqlDataRecord(
        //        new SqlMetaData("ra", SqlDbType.Float),
        //        new SqlMetaData("dec", SqlDbType.Float),
        //        new SqlMetaData("cd", SqlDbType.Float),
        //        new SqlMetaData("count", SqlDbType.Int),
        //        new SqlMetaData("xd", SqlDbType.Float)
        //        );

        //    //Return values
        //    SqlContext.Pipe.SendResultsStart(s);
        //    //s.SetDouble(0, avgra / count);
        //    //s.SetDouble(1, avgdec / count);
        //    s.SetDouble(0, 0.0);
        //    s.SetDouble(1, 0.0);
        //    s.SetDouble(2, cd / (count * (count - 1) / 2));
        //    s.SetInt32(3, count * (count - 1));
        //    s.SetDouble(4, xd / (count * (count - 1) / 2));

        //    SqlContext.Pipe.SendResultsRow(s);
        //    SqlContext.Pipe.SendResultsEnd();


        //}

        ////Cover index created for this!
        //[Microsoft.SqlServer.Server.SqlProcedure]
        //public static void ClusterSearch3(SqlDouble ra, SqlDouble dec, SqlDouble R)
        //{



        //    double nr = 2 * Math.Sin((((double)(R / 120)) / 180.0 * Math.PI));
        //    double nx, ny, nz, decr, rar;
        //    decr = Math.PI / 180 * (double)dec;
        //    rar = Math.PI / 180 * (double)ra;
        //    nx = Math.Cos(decr) * Math.Cos(rar);
        //    ny = Math.Cos(decr) * Math.Sin(rar);
        //    nz = Math.Sin(decr);

        //    //results
        //    double avgra = 0;
        //    double avgdec = 0;
        //    double cd = 0;
        //    double xd = 0;
        //    int count = 0;


        //    //to change, works, speedy, but ugly
        //    double[] r = new double[10000];
        //    double[] gr = new double[10000];
        //    double tempr;
        //    double tempgr;
        //    double tempcd;
        //    double x, y, z;

        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString)) //"Context connection=yes"
        //    {
        //        cn.Open();
        //        Indices.BalancedTreeIndex I = (Indices.BalancedTreeIndex)Elte.SqlSpIdx.Index.LoadIndex(cn, "K1", IndexType.BalancedTree);
        //        IEnumerator result = I.UltimateQuery(
        //                @"cast(psfMag_r as float) as r,cast((psfMag_g-psfMag_r) as float) as gr ",
        //               @"distance(cx=" + nx + ",cy=" + ny + ",cz=" + nz + ")<" + (nr).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat),
        //                @"psfMag_r between 14 and 22 and psfMag_g-psfMag_r between -0.5 and 2.5").GetEnumerator();

        //        while (result.MoveNext())
        //        {
        //            object[] values = (object[])result.Current;

        //            //z = (double)values[2];
        //            //decr = Math.Asin(z);

        //            //y = (double)values[1];
        //            //rar = Math.Asin(y / Math.Cos(decr));


        //            //decr = Math.PI / 180 * (double)dec;
        //            //rar = Math.PI / 180 * (double)ra;
        //            //nx = Math.Cos(decr) * Math.Cos(rar);
        //            //ny = Math.Cos(decr) * Math.Sin(rar);
        //            //nz = Math.Sin(decr);


        //            ////avg of ra
        //            //avgra += rar * 180 / Math.PI;

        //            ////avg of dec
        //            //avgdec += decr * 180 / Math.PI;

        //            //r values
        //            tempr = (double)values[0];
        //            r[count] = tempr;

        //            //gr values
        //            tempgr = (double)values[1];
        //            gr[count] = tempgr;

        //            for (int i = 0; i < count; i++)
        //            {
        //                tempcd = (r[i] - tempr) * (r[i] - tempr) + (gr[i] - tempgr) * (gr[i] - tempgr);
        //                cd += tempcd;
        //                xd += Math.Exp(-tempcd);
        //            }
        //            count++;

        //        }

        //    }
        //    if (SqlContext.IsAvailable) { SqlContext.Pipe.Send(Elte.SqlSpIdx.Indices.BalancedTreeIndex.output); }

        //    if (SqlContext.IsAvailable)
        //    {
        //        //Return structure
        //        SqlDataRecord s = new SqlDataRecord(
        //            new SqlMetaData("ra", SqlDbType.Float),
        //            new SqlMetaData("dec", SqlDbType.Float),
        //            new SqlMetaData("cd", SqlDbType.Float),
        //            new SqlMetaData("count", SqlDbType.Int),
        //            new SqlMetaData("xd", SqlDbType.Float)
        //            );

        //        //Return values
        //        SqlContext.Pipe.SendResultsStart(s);
        //        s.SetDouble(0, (double)ra);
        //        s.SetDouble(1, (double)dec);
        //        //s.SetDouble(0, 0.0);
        //        //s.SetDouble(1, 0.0);
        //        s.SetDouble(2, cd / (count * (count - 1) / 2));
        //        s.SetInt32(3, count * (count - 1));
        //        s.SetDouble(4, xd / (count * (count - 1) / 2));


        //        SqlContext.Pipe.SendResultsRow(s);
        //        SqlContext.Pipe.SendResultsEnd();

        //    }



        //}



        ////Cover index created for this!
        //[Microsoft.SqlServer.Server.SqlFunction(
        //   DataAccess = DataAccessKind.Read, FillRowMethodName = "ClusterSearchFillRow",
        //    IsDeterministic = true, IsPrecise = true,
        //    TableDefinition = "ra float,dec float,cd float,count float,xd float")]
        //public static IEnumerable ClusterSearch4(SqlDouble ra, SqlDouble dec, SqlDouble R)
        //{
        //    double nr = 2 * Math.Sin((((double)(R / 120)) / 180.0 * Math.PI));
        //    double nx, ny, nz, decr, rar;
        //    decr = Math.PI / 180 * (double)dec;
        //    rar = Math.PI / 180 * (double)ra;
        //    nx = Math.Cos(decr) * Math.Cos(rar);
        //    ny = Math.Cos(decr) * Math.Sin(rar);
        //    nz = Math.Sin(decr);

        //    //results
        //    double avgra = 0;
        //    double avgdec = 0;
        //    double cd = 0;
        //    double xd = 0;
        //    int count = 0;


        //    //to change, works, speedy, but ugly
        //    double[] r = new double[10000];
        //    double[] gr = new double[10000];
        //    double tempr;
        //    double tempgr;
        //    double tempcd;
        //    double x, y, z;

        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString)) //"Context connection=yes"
        //    {
        //        cn.Open();
        //        Indices.BalancedTreeIndex I = (Indices.BalancedTreeIndex)Elte.SqlSpIdx.Index.LoadIndex(cn, "K1", IndexType.BalancedTree);
        //        IEnumerator result = I.UltimateQuery(
        //                @"cast(psfMag_r as float) as r,cast((psfMag_g-psfMag_r) as float) as gr ",
        //               @"distance(cx=" + nx + ",cy=" + ny + ",cz=" + nz + ")<" + (nr).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat),
        //                @"psfMag_r between 14 and 22 and psfMag_g-psfMag_r between -0.5 and 2.5").GetEnumerator();

        //        while (result.MoveNext())
        //        {
        //            object[] values = (object[])result.Current;



        //            //r values
        //            tempr = (double)values[0];
        //            r[count] = tempr;

        //            //gr values
        //            tempgr = (double)values[1];
        //            gr[count] = tempgr;

        //            for (int i = 0; i < count; i++)
        //            {
        //                tempcd = (r[i] - tempr) * (r[i] - tempr) + (gr[i] - tempgr) * (gr[i] - tempgr);
        //                cd += tempcd;
        //                xd += Math.Exp(-tempcd);
        //            }
        //            count++;

        //        }

        //    }
        //    List<double[]> l = new List<double[]>();
        //    l.Add(new double[] { (double)ra, (double)dec, cd / (count * (count - 1) / 2), count * (count - 1), xd / (count * (count - 1) / 2) });
        //    return l;
        //}

        ////Cover index created for this!
        //[Microsoft.SqlServer.Server.SqlProcedure]
        //public static void ClusterSearch5(SqlDouble ra, SqlDouble dec)
        //{

        //    double nx, ny, nz, decr, rar;
        //    decr = Math.PI / 180 * (double)dec;
        //    rar = Math.PI / 180 * (double)ra;
        //    nx = Math.Cos(decr) * Math.Cos(rar);
        //    ny = Math.Cos(decr) * Math.Sin(rar);
        //    nz = Math.Sin(decr);

        //    //results
        //    double avgra = 0;
        //    double avgdec = 0;
        //    double cd = 0;
        //    double xd = 0;
        //    int count = 0;


        //    //to change, works, speedy, but ugly
        //    double[] r = new double[10000];
        //    double[] gr = new double[10000];
        //    double tempr;
        //    double tempgr;
        //    double tempcd;
        //    SqlContext.Pipe.Send("");
        //    using (SqlConnection cn = new SqlConnection("Context connection=yes"))
        //    {
        //        cn.Open();


        //        //SP
        //        SqlCommand ClusterSearchTsql = new SqlCommand("ClusterSearchTsql5");
        //        ClusterSearchTsql.CommandType = CommandType.StoredProcedure;
        //        ClusterSearchTsql.Parameters.AddWithValue("@x", nx);
        //        ClusterSearchTsql.Parameters.AddWithValue("@y", ny);
        //        ClusterSearchTsql.Parameters.AddWithValue("@z", nz);
        //        ClusterSearchTsql.Connection = cn;

        //        using (SqlDataReader rs = ClusterSearchTsql.ExecuteReader())
        //        {
        //            while (rs.Read())
        //            {

        //                //r values
        //                tempr = rs.GetDouble(0);
        //                r[count] = tempr;

        //                //gr values
        //                tempgr = rs.GetDouble(1);
        //                gr[count] = tempgr;




        //                for (int i = 0; i < count; i++)
        //                {

        //                    tempcd = (r[i] - tempr) * (r[i] - tempr) + (gr[i] - tempgr) * (gr[i] - tempgr);

        //                    //instead of 'avg((s1.r-s2.r)*(s1.r-s2.r)+(s1.gr-s2.gr)*(s1.gr-s2.gr)) as cd'
        //                    cd += tempcd;

        //                    //instead of 'avg(exp(-((s1.r-s2.r)*(s1.r-s2.r)+(s1.gr-s2.gr)*(s1.gr-s2.gr)))) as xd'
        //                    xd += Math.Exp(-tempcd); ;
        //                }
        //                count++;
        //            }

        //        }

        //    }
        //    //Return structure
        //    SqlDataRecord s = new SqlDataRecord(
        //        new SqlMetaData("ra", SqlDbType.Float),
        //        new SqlMetaData("dec", SqlDbType.Float),
        //        new SqlMetaData("cd", SqlDbType.Float),
        //        new SqlMetaData("count", SqlDbType.Int),
        //        new SqlMetaData("xd", SqlDbType.Float)
        //        );

        //    //Return values
        //    SqlContext.Pipe.SendResultsStart(s);
        //    s.SetDouble(0, (double)ra);
        //    s.SetDouble(1, (double)dec);
        //    s.SetDouble(2, cd / (count * (count - 1) / 2));
        //    s.SetInt32(3, count * (count - 1));
        //    s.SetDouble(4, xd / (count * (count - 1) / 2));

        //    SqlContext.Pipe.SendResultsRow(s);
        //    SqlContext.Pipe.SendResultsEnd();


        //}



        ////Cover index created for this!
        //[Microsoft.SqlServer.Server.SqlFunction(
        //   DataAccess = DataAccessKind.Read, FillRowMethodName = "ClusterSearchFillRow2",
        //    IsDeterministic = true, IsPrecise = true,
        //    TableDefinition = "r float,g float")]
        //public static IEnumerable ClusterSearch6(SqlDouble x, SqlDouble y, SqlDouble z, SqlDouble R)
        //{
        //    double r = 2 * Math.Sin((((double)(R / 120)) / 180.0 * Math.PI));
        //    List<double[]> finalresult = new List<double[]>();
        //    using (SqlConnection cn = new SqlConnection(Constants.MyConnectionString)) //"Context connection=yes"
        //    {
        //        cn.Open();
        //        Indices.BalancedTreeIndex I = (Indices.BalancedTreeIndex)Elte.SqlSpIdx.Index.LoadIndex(cn, "K1", IndexType.BalancedTree);
        //        IEnumerator result = I.UltimateQuery(
        //                @"cast(psfMag_r as float) as r,cast((psfMag_g-psfMag_r) as float) as gr ",
        //               @"distance(cx=" + ((double)x).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat) + ",cy=" + ((double)y).ToString((System.Globalization.CultureInfo.InvariantCulture.NumberFormat)) + ",cz=" + ((double)z).ToString((System.Globalization.CultureInfo.InvariantCulture.NumberFormat)) + ")<" + (r).ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat),
        //                @"psfMag_r between 14 and 22 and psfMag_g-psfMag_r between -0.5 and 2.5").GetEnumerator();

        //        while (result.MoveNext())
        //        {
        //            object[] values = (object[])result.Current;

        //            finalresult.Add(new double[2] { (double)values[0], (double)values[1] });
        //        }

        //    }
        //    return finalresult;
        //}



    }
}
